INSERT INTO Setting(Name, Value) VALUES('FaultTraceToolURL', 'http://king/faulttracetool/api.html')
GO

ALTER PROCEDURE [dbo].[selectSitesFaultsDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startDate DateTime = @EventDate
    DECLARE @endDate DateTime

    if @context = 'day'
        SET @endDate = DATEADD(DAY, 1, @startDate);
    if @context = 'hour'
        SET @endDate = DATEADD(HOUR, 1, @startDate);
    if @context = 'minute'
        SET @endDate = DATEADD(MINUTE, 1, @startDate);
    if @context = 'second'
        SET @endDate = DATEADD(SECOND, 1, @startDate);

    SELECT *
    INTO #authMeters
    FROM authMeters(@username)

    SELECT *
    INTO #meterSelections
    FROM String_to_int_table(@MeterID, ',')

    ; WITH FaultDetail AS
    (
        SELECT
            FaultSummary.ID AS thefaultid,
            Meter.Name AS thesite,
            Meter.ShortName AS theshortsite,
            MeterLocation.ShortName AS locationname,
            Meter.ID AS themeterid,
            Line.ID AS thelineid,
            Event.ID AS theeventid,
            MeterLine.LineName AS thelinename,
			Line.AssetKey AS thelinekey,
            Line.VoltageKV AS voltage,
            CAST(CAST(Event.StartTime AS TIME) AS NVARCHAR(100)) AS theinceptiontime,
			FaultSummary.Inception AS therealinceptiontime,
            FaultSummary.FaultType AS thefaulttype,
            CASE WHEN FaultSummary.Distance = '-1E308' THEN 'NaN' ELSE CAST(CAST(FaultSummary.Distance AS DECIMAL(16,2)) AS NVARCHAR(19)) END AS thecurrentdistance,
			(SELECT Value FROM Setting WHERE Name = 'FaultTraceToolURL') +
				'?lineid=' + SUBSTRING(Line.AssetKey, PATINDEX('%[^0]%', Line.AssetKey + '.'), LEN(Line.AssetKey)) +
				'&stationname=' + MeterLocation.Name +
				'&distance=' + CASE WHEN FaultSummary.Distance = '-1E308'
					THEN '-1'
					ELSE CAST(CAST(FaultSummary.Distance AS DECIMAL(16,2)) AS VARCHAR(19))
				END +
				'&eventtime=' + FORMAT(FaultSummary.Inception, 'yyyy-MM-dd HH:mm:ss.fffffff')
			AS ftt,
            (SELECT COUNT(*) FROM FaultNote WHERE FaultSummary.ID = FaultNote.FaultSummaryID) as notecount,
            ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY FaultSummary.IsSuppressed, FaultSummary.IsSelectedAlgorithm DESC, FaultSummary.Inception) AS rk
        FROM
            FaultSummary JOIN
            Event ON FaultSummary.EventID = Event.ID JOIN
            EventType ON Event.EventTypeID = EventType.ID JOIN
            Meter ON Event.MeterID = Meter.ID JOIN
            MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN
            Line ON Event.LineID = Line.ID JOIN
            MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Line.ID
        WHERE
            EventType.Name = 'Fault' AND
            Event.StartTime >= @startDate AND Event.StartTime < @endDate AND
            Meter.ID IN (SELECT * FROM #meterSelections) AND
            Meter.ID IN (select * from #authMeters)
    ),
	OutageDetail AS
	(
		SELECT
			thefaultid,
			thesite,
			theshortsite,
			locationname,
			themeterid,
			thelineid,
			theeventid,
			thelinename,
			voltage,
			theinceptiontime,
			thefaulttype,
			thecurrentdistance,
            ftt,
			notecount,
			'[' + CAST(Outages.MirsCauseID AS VARCHAR(10)) + '] ' + Outages.CauseDesc AS causecode,
			ROW_NUMBER() OVER(PARTITION BY theeventid ORDER BY ABS(DATEDIFF(MICROSECOND, Outages.StartTime, therealinceptiontime))) AS rk
		FROM
			FaultDetail LEFT OUTER JOIN
			Outages ON
				Outages.LineId = FaultDetail.thelinekey AND
				therealinceptiontime BETWEEN DATEADD(MINUTE, -5, Outages.StartTime) AND DATEADD(MINUTE, 5, Outages.StartTime)
		WHERE rk = 1
	)
    SELECT *
    FROM OutageDetail
    WHERE rk = 1
END
GO